const Base = require('./base.js');

module.exports = class extends Base {
  /*
  *生态店务
  */
  //获取当前机构的门店列表
  async selectshopAction(){
    let orcode = this.post('orcode');
    const mypath = think.config('mypath');
    let getapi = this.controller(mypath+'/database');
    let info = await getapi.getsystemshoplist(orcode);//记录数量
    return think.isEmpty(info)?this.json({'code':"200",'msg':"没有数据...",'data':''}):this.json({'code':"100",'msg':"查找成功...",'data':info});
  }

  //获取生态店务板块年度数据
  async getshopnianduAction(){
    let shopid = this.post('shopid');
    let year = this.post('year');
    //获取今年开始的时间戳
    var start_time_year =  Math.round(new Date(year,0, 1).getTime()/1000);
    //今年结束时间
    var end_time_year =  Math.round(new Date(Number(year) + 1,0, 1).getTime()/1000);
    let orcodeinfo = await this.model().query('SELECT pid FROM `think_or_department` WHERE id = '+shopid+' ');
    let orid = orcodeinfo[0].pid;
    let info = await this.model().query('SELECT * FROM `think_or_department_info` WHERE departmentid = '+shopid+' ');
    const mypath = think.config('mypath');
    let getapi = this.controller(mypath+'/database');
    let worknum =  await getapi.getshopworknum(shopid);
    let allguestnum =  await getapi.getshopallguestnum(shopid);
    let ableguestnum =  await getapi.getshopallguestnum(shopid, 0);
    
    // let fensinum =  await getapi.getshopfensinum(shopid);
    // let brandnum =  await getapi.getorbrandnum(orid);
    // let factorynum =  await getapi.getorfactorynum(orid);
    let xianjin = await getapi.getyejixianjin(start_time_year,end_time_year,1,shopid,0,0);//销售业绩
    let cardreduce = await getapi.getyejikahao(start_time_year,end_time_year,1,shopid,0,0);//卡耗业绩
    let shopinfo = {'worknum':worknum,'allguestnum':allguestnum,'ableguestnum':ableguestnum,'xianjin':xianjin,'cardreduce':cardreduce} ; 
    return this.json({'code':"100",'msg':"查找成功...",'data':info,'shopinfo':shopinfo });
  }

  //获取
  async getshopyejibumenAction(){
    let shopid = this.post('shopid');
    let start_time = this.post('start_time_js');
    let end_time = this.post('end_time_js');

    let info = await this.model().query(' SELECT id,name FROM `think_or_department` od WHERE pid = '+shopid+' ');
    let data = [];
    for(var i = 0; i < info.length ; i ++){
      let feat = await this.model().query(' SELECT IFNULL(sum(osy.feat),0) as feat FROM `think_or_shop_yejixianjin` osy LEFT JOIN `think_or_department` od ON osy.bumenid = od.id WHERE (od.id = '+info[i].id+' or od.pid = '+info[i].id+') AND osy.addtime >= '+start_time+' AND osy.addtime < '+end_time+' '); 
      data.push({'id':info[i].id,'name':info[i].name,'feat':feat[0].feat});
    }
    return think.isEmpty(info)?this.json({'code':"200",'msg':"没有数据...",'data':''}):this.json({'code':"100",'msg':"查找成功...",'data':data});
  }

  //获取有效会员产值的接口
  async getguestchangzhiAction(){
    let shopid = this.post('shopid');
    let year = this.post('year');
    //获取今年开始的时间戳
    var start_time_year =  Math.round(new Date(year,0, 1).getTime()/1000);
    //今年结束时间
    var end_time_year =  Math.round(new Date(year,11, 1).getTime()/1000); 
    //获取去年的开始时间
    var start_time_year_last =  Math.round(new Date(Number(year) - 1,0, 1).getTime()/1000);
    //获取去年的结束时间
    var end_time_year_last =  Math.round(new Date(Number(year) - 1,11, 1).getTime()/1000);

    let info = await this.model().query(' SELECT og.id,og.name,og.addtime,if(og.sex=1,"男","女") as sex,(year(now()) - IFNULL(oga.birthyear,0)) as birthyear,og.lv,IFNULL(B.maxbuy,0) as maxbuy,IFNULL(C.thisyearfeat,0) as thisyearfeat,IFNULL(D.lastyearfeat,0) as lastyearfeat,IFNULL(E.num,0) as cardnum,IFNULL(F.num,0) as fnum,og.balance FROM `think_or_guest` og LEFT JOIN `think_or_guest_archives` oga ON og.id = oga.guestid LEFT JOIN ( SELECT guestid,max(recemoney) as maxbuy FROM `think_or_shop_order` WHERE departmentid = '+shopid+' GROUP BY guestid ) as B ON og.id = B.guestid LEFT JOIN ( SELECT guestid,sum(recemoney) as thisyearfeat FROM `think_or_shop_order` WHERE departmentid = '+shopid+' AND (status = 1 or status = 2) AND addtime >= '+start_time_year+' AND addtime < '+end_time_year+' GROUP BY guestid) as C ON og.id = C.guestid LEFT JOIN ( SELECT guestid,sum(recemoney) as lastyearfeat FROM `think_or_shop_order` WHERE departmentid = '+shopid+' AND (status = 1 or status = 2) AND addtime >= '+start_time_year_last+' AND addtime < '+end_time_year_last+' GROUP BY guestid) as D ON og.id = D.guestid LEFT JOIN (SELECT guestid,count(*) as num FROM `think_or_shop_guestcard` WHERE departmentid = '+shopid+' GROUP BY guestid) as E ON og.id = E.guestid LEFT JOIN ( SELECT oat.gid,count(*) as num FROM `think_or_allotuser` oat LEFT JOIN `think_or_guest` og ON oat.gid = og.id WHERE og.departmentid = '+shopid+' GROUP BY oat.gid ) as F ON og.id = F.gid WHERE og.departmentid = '+shopid+' ');
    return think.isEmpty(info)?this.json({'code':"200",'msg':"没有数据...",'data':''}):this.json({'code':"100",'msg':"查找成功...",'data':info});
  }

  //获取去年会员12个月的业绩
  async getguestlastyearbuyAction(){
    let shopid = this.post('shopid');
    let year = this.post('year'); 
    //获取今年开始的时间戳
    var start_time_year =  Math.round(new Date(year,0, 1).getTime()/1000);
    //今年结束时间
    var end_time_year =  Math.round(new Date(year,11, 1).getTime()/1000); 

    let info = await this.model().query('SELECT oso.guestid as id,if(og.sex=1,"男","女") as sex,og.lv,og.name,sum(if(month(FROM_UNIXTIME(oso.addtime))=1,oso.recemoney,0)) as Jan,sum(if(month(FROM_UNIXTIME(oso.addtime))=2,oso.recemoney,0)) as Feb,sum(if(month(FROM_UNIXTIME(oso.addtime))=3,oso.recemoney,0)) as Mar,sum(if(month(FROM_UNIXTIME(oso.addtime))=4,oso.recemoney,0)) as Apr,sum(if(month(FROM_UNIXTIME(oso.addtime))=5,oso.recemoney,0)) as May,sum(if(month(FROM_UNIXTIME(oso.addtime))=6,oso.recemoney,0)) as June,sum(if(month(FROM_UNIXTIME(oso.addtime))=7,oso.recemoney,0)) as July,sum(if(month(FROM_UNIXTIME(oso.addtime))=8,oso.recemoney,0)) as Aug,sum(if(month(FROM_UNIXTIME(oso.addtime))=9,oso.recemoney,0)) as Sept,sum(if(month(FROM_UNIXTIME(oso.addtime))=10,oso.recemoney,0)) as Oct,sum(if(month(FROM_UNIXTIME(oso.addtime))=11,oso.recemoney,0)) as Nov,sum(if(month(FROM_UNIXTIME(oso.addtime))=12,oso.recemoney,0)) as Dect FROM `think_or_shop_order` oso LEFT JOIN `think_or_guest` og ON oso.guestid = og.id WHERE oso.departmentid = '+shopid+' AND oso.addtime >= '+start_time_year+' AND oso.addtime < '+end_time_year+' group by oso.guestid'  );

    return think.isEmpty(info)?this.json({'code':"200",'msg':"没有数据...",'data':''}):this.json({'code':"100",'msg':"查找成功...",'data':info});
  }

  //获取年度客户消费结构表
  async getguestbuyjiegouAction(){
    let shopid = this.post('shopid');
    let start_time = this.post('start_time_js');
    let end_time = this.post('end_time_js');
    const mypath = think.config('mypath');
    let getapi = this.controller(mypath+'/database');
    let allguestnum =  await getapi.getshopallguestnum(shopid);

    let info = await this.model().query(' SELECT og.id,og.name,IFNULL(A.feat,0) as feat FROM `think_or_guest` og LEFT JOIN ( SELECT guestid,sum(recemoney) as feat FROM `think_or_shop_order` WHERE departmentid = '+shopid+'  AND addtime >= '+start_time+' AND addtime < '+end_time+' GROUP BY guestid ) as A ON og.id = A.guestid WHERE og.departmentid ='+shopid+'  ');

    return think.isEmpty(info)?this.json({'code':"200",'msg':"没有数据...",'data':''}):this.json({'code':"100",'msg':"查找成功...",'data':info,'allguestnum':allguestnum});
  }

  //获取客户到店次数表数据
  async getguestdaodianAction(){
    let shopid = this.post('shopid');
    let start_time = this.post('start_time_js');
    let end_time = this.post('end_time_js');

    let info = await this.model().query(' SELECT og.id,if(og.sex=1,"男","女") as sex,og.name,og.lv,IFNULL(A.num,0) as num,IFNULL(A.feat,0) as feat FROM `think_or_guest` og LEFT JOIN (SELECT guestid,count(*) as num,sum(recemoney) as feat FROM `think_or_shop_order` WHERE departmentid = '+shopid+' and status != 0  AND addtime >= '+start_time+' AND addtime < '+end_time+' GROUP BY guestid) as A ON og.id = A.guestid WHERE og.departmentid = '+shopid+' ');

    const mypath = think.config('mypath');
    let getapi = this.controller(mypath+'/database');
    let xianjin = await this.model().query(' SELECT IFNULL(sum(osy.xianjinyeji),0) as amount FROM `think_or_shop_yejiticheng` osy LEFT JOIN `think_or_app_user` au ON osy.userid = au.id WHERE osy.addtime >= ' + start_time + ' AND osy.addtime < ' + end_time + ' AND au.shopid = ' + shopid + ' and osy.ordertype != "TKD" and osy.ordertype != "TQD"')
    if (think.isEmpty(xianjin)) { xianjin = 0 } else { xianjin =  xianjin[0].amount }
    return think.isEmpty(info)?this.json({'code':"200",'msg':"没有数据...",'data':''}):this.json({'code':"100",'msg':"查找成功...",'data':info,'xianjin':xianjin});
  }

  //获取客户业绩增涨比例
  async getguestzengzhangAction(){
    let shopid = this.post('shopid');
    let year = this.post('year'); 
    //获取今年开始的时间戳
    var start_time_year =  Math.round(new Date(year,0, 1).getTime()/1000);
    //今年结束时间
    var end_time_year =  Math.round(new Date(Number(year) + 1,0, 1).getTime()/1000); 
    //获取去年的开始时间
    var start_time_year_last =  Math.round(new Date(Number(year) - 1,0, 1).getTime()/1000);
    //获取去年的结束时间
    var end_time_year_last =  Math.round(new Date(year,0, 1).getTime()/1000); 

    let info = await this.model().query(' SELECT og.id,og.name,if(og.sex=1,"男","女") as sex,og.lv,IFNULL(A.feat,0) as feat,IFNULL(B.feat,0) as planfeat,IFNULL(C.feat,0) as realfeat FROM `think_or_guest` og LEFT JOIN (SELECT guestid,sum(recemoney) as feat  FROM `think_or_shop_order` WHERE  departmentid = '+shopid+'  AND addtime >= '+start_time_year_last+' AND addtime < '+end_time_year_last+' GROUP BY guestid) as A ON A.guestid = og.id LEFT JOIN (SELECT py.guestid,py.feat FROM `think_plan_year` py LEFT JOIN `think_or_guest` og ON py.guestid = og.id WHERE py.year = '+year+' AND og.departmentid = '+shopid+') as B ON og.id = B.guestid LEFT JOIN (SELECT guestid,sum(recemoney) as feat FROM `think_or_shop_order` WHERE departmentid = '+shopid+'  AND addtime >= '+start_time_year+' AND addtime < '+end_time_year+' GROUP BY guestid) as C ON og.id = C.guestid WHERE og.departmentid = '+shopid+' ');

    return think.isEmpty(info)?this.json({'code':"200",'msg':"没有数据...",'data':''}):this.json({'code':"100",'msg':"查找成功...",'data':info});
  }

  //获取员工年度产值表
  async getworkerchanzhiAction(){
    let shopid = this.post('shopid');
    let start_time_js = this.post('start_time_js'); 
    let end_time_js = this.post('end_time_js'); 

    let info = await this.model().query('SELECT au.id,au.name,IFNULL(p.name,0) as position,IFNULL(A.num,0) as fuwunum,IFNULL(A.xj,0) as xjyj,IFNULL(A.kh,0) as khyj FROM `think_or_app_user` au LEFT JOIN `think_or_position` p ON au.positionid = p.id LEFT JOIN ( SELECT syt.userid,count(DISTINCT syt.guestid) as num,sum(syt.xianjinyeji) as xj,sum(syt.kahaoyeji) as kh FROM `think_or_shop_yejiticheng` syt WHERE syt.addtime >= '+start_time_js+' AND syt.addtime<= '+end_time_js+' GROUP BY syt.userid ) as A on au.id = A.userid WHERE au.shopid = '+shopid+' AND au.isdelete = 0 AND au.isdisable = 0');
    return think.isEmpty(info)?this.json({'code':"200",'msg':"没有数据...",'data':''}):this.json({'code':"100",'msg':"查找成功...",'data':info});
  }

  //获取员工全年业绩统计表
  async getworkeryearfeatAction(){
    let shopid = this.post('shopid');
    let year = parseInt(this.post('year')); 
    //获取今年开始的时间戳
    var start_time_year =  Math.round(new Date(year,0, 1).getTime()/1000);
    //今年结束时间
    var end_time_year =  Math.round(new Date(Number(year) + 1,0, 1).getTime()/1000); 

    let info = await this.model().query('SELECT u.id,u.name, IFNULL(p.Jan, 0) AS Jan,IFNULL(p.Feb, 0) AS Feb,IFNULL(p.Mar, 0) AS Mar,IFNULL(p.Apr, 0) AS Apr,IFNULL(p.May, 0) AS May,IFNULL(p.June, 0) AS June,IFNULL(p.July, 0) AS July,IFNULL(p.Aug, 0) AS Aug,IFNULL(p.Sept, 0) AS Sept,IFNULL(p.Oct, 0) AS Oct,IFNULL(p.Nov, 0) AS Nov,IFNULL(p.Dect, 0) AS Dect FROM `think_or_app_user` u LEFT JOIN ( SELECT oso.userid,sum(if(month(FROM_UNIXTIME(oso.addtime))=1,oso.feat,0)) as Jan,sum(if(month(FROM_UNIXTIME(oso.addtime))=2,oso.feat,0)) as Feb,sum(if(month(FROM_UNIXTIME(oso.addtime))=3,oso.feat,0)) as Mar,sum(if(month(FROM_UNIXTIME(oso.addtime))=4,oso.feat,0)) as Apr,sum(if(month(FROM_UNIXTIME(oso.addtime))=5,oso.feat,0)) as May,sum(if(month(FROM_UNIXTIME(oso.addtime))=6,oso.feat,0)) as June,sum(if(month(FROM_UNIXTIME(oso.addtime))=7,oso.feat,0)) as July,sum(if(month(FROM_UNIXTIME(oso.addtime))=8,oso.feat,0)) as Aug,sum(if(month(FROM_UNIXTIME(oso.addtime))=9,oso.feat,0)) as Sept,sum(if(month(FROM_UNIXTIME(oso.addtime))=10,oso.feat,0)) as Oct,sum(if(month(FROM_UNIXTIME(oso.addtime))=11,oso.feat,0)) as Nov,sum(if(month(FROM_UNIXTIME(oso.addtime))=12,oso.feat,0)) as Dect FROM `think_or_shop_yejixianjin` oso WHERE oso.addtime >= '+start_time_year+' AND oso.addtime < '+end_time_year+' group by oso.userid ) as p on u.id = p.userid WHERE u.shopid = '+shopid+' AND u.isdisable = 0 AND u.isdelete = 0');

    return think.isEmpty(info)?this.json({'code':"200",'msg':"没有数据...",'data':''}):this.json({'code':"100",'msg':"查找成功...",'data':info});
  }

  //-------------------------------------------------------------------------品牌类数据分析-----------------------------------------------
  //获取品牌经营数据分析
  async getbrandjingyingAction(){
    let shopid = this.post('shopid');
    let start_time = this.post('start_time_js');
    let end_time = this.post('end_time_js');

    let orcodeinfo = await this.model().query('SELECT pid FROM `think_or_department` WHERE id = '+shopid+' ');
    let orid = orcodeinfo[0].pid;
    let info = await this.model().query('SELECT ob.id,ob.name as brandname,of.name as factoryname,IFNULL(A.num,0) as subjectnum,IFNULL(B.num,0) as goodsnum,IFNULL(C.feat,0) as subjectfeat,IFNULL(D.feat,0) as goodsfeat,IFNULL(E.num,0) guestnum FROM `think_or_brand` ob LEFT JOIN `think_or_factory` of ON ob.belongs = of.id LEFT JOIN (SELECT s.brandid,count(*) as num FROM `think_or_shop_subject` sb left join `think_or_subject` s on sb.subjectid = s.id WHERE sb.shopid = '+shopid+' GROUP BY s.brandid) as A ON ob.id = A.brandid LEFT JOIN (SELECT g.brandid,count(*) as num FROM `think_or_shop_goods` sg left join `think_or_goods` g on sg.goodsid = g.id WHERE sg.shopid = '+shopid+' GROUP BY g.brandid) as B ON ob.id = B.brandid LEFT JOIN (SELECT osoi.brandid,sum(osoi.amount) as feat FROM `think_or_shop_orderinfo` osoi LEFT JOIN `think_or_shop_order` oso ON osoi.orderid = oso.id WHERE oso.addtime >= '+start_time+' AND oso.addtime < '+end_time+' AND osoi.departmentid = '+shopid+' AND osoi.type = 2 GROUP BY osoi.brandid ) as C ON ob.id = C.brandid LEFT JOIN (SELECT osoi.brandid,sum(osoi.amount) as feat FROM `think_or_shop_orderinfo` osoi LEFT JOIN `think_or_shop_order` oso ON osoi.orderid = oso.id WHERE oso.addtime >= '+start_time+' AND oso.addtime < '+end_time+' AND osoi.departmentid = '+shopid+' AND osoi.type = 1 GROUP BY osoi.brandid ) as D ON ob.id = D.brandid LEFT JOIN (SELECT osoi.brandid,count(distinct oso.guestid)as num FROM `think_or_shop_orderinfo` osoi LEFT JOIN `think_or_shop_order` oso ON osoi.orderid = oso.id WHERE oso.addtime >= '+start_time+' AND oso.addtime < '+end_time+' AND osoi.departmentid = '+shopid+' AND osoi.brandid <> 0 GROUP BY osoi.brandid) as E ON ob.id = E.brandid WHERE ob.departmentid = '+orid+'  ');
    const mypath = think.config('mypath');
    let getapi = this.controller(mypath+'/database');
    let allguestnum =  await getapi.getshopallguestnum(shopid);

    return think.isEmpty(info)?this.json({'code':"200",'msg':"没有数据...",'data':''}):this.json({'code':"100",'msg':"查找成功...",'data':info,'allguestnum':allguestnum});
  }

  //获取品牌每月业绩
  async getbrandjingyingyejiAction(){
    let shopid = this.post('shopid');
    let year = this.post('year');
    //获取今年开始的时间戳
    var start_time_year =  Math.round(new Date(year,0, 1).getTime()/1000);
    //今年结束时间
    var end_time_year =  Math.round(new Date(Number(year) + 1,0, 1).getTime()/1000); 

    let orcodeinfo = await this.model().query('SELECT pid FROM `think_or_department` WHERE id = '+shopid+' ');
    let orid = orcodeinfo[0].pid;
    let info = await this.model().query(' SELECT ob.id,ob.name as brandname,IFNULL(A.Jan,0) as Jan,IFNULL(A.Feb,0) as Feb,IFNULL(A.Mar,0) as Mar,IFNULL(A.Apr,0) as Apr,IFNULL(A.May,0) as May,IFNULL(A.June,0) as June,IFNULL(A.July,0) as July,IFNULL(A.Aug,0) as Aug,IFNULL(A.Sept,0) as Sept,IFNULL(A.Oct,0) as Oct,IFNULL(A.Nov,0) as Nov,IFNULL(A.Dect,0) as Dect FROM `think_or_brand` ob LEFT JOIN (SELECT osoi.brandid,sum(if(month(FROM_UNIXTIME(oso.addtime))=1,osoi.amount,0)) as Jan,sum(if(month(FROM_UNIXTIME(oso.addtime))=2,osoi.amount,0)) as Feb,sum(if(month(FROM_UNIXTIME(oso.addtime))=3,osoi.amount,0)) as Mar,sum(if(month(FROM_UNIXTIME(oso.addtime))=4,osoi.amount,0)) as Apr,sum(if(month(FROM_UNIXTIME(oso.addtime))=5,osoi.amount,0)) as May,sum(if(month(FROM_UNIXTIME(oso.addtime))=6,osoi.amount,0)) as June,sum(if(month(FROM_UNIXTIME(oso.addtime))=7,osoi.amount,0)) as July,sum(if(month(FROM_UNIXTIME(oso.addtime))=8,osoi.amount,0)) as Aug,sum(if(month(FROM_UNIXTIME(oso.addtime))=9,osoi.amount,0)) as Sept,sum(if(month(FROM_UNIXTIME(oso.addtime))=10,osoi.amount,0)) as Oct,sum(if(month(FROM_UNIXTIME(oso.addtime))=11,osoi.amount,0)) as Nov,sum(if(month(FROM_UNIXTIME(oso.addtime))=12,osoi.amount,0)) as Dect FROM `think_or_shop_orderinfo` osoi LEFT JOIN `think_or_shop_order` oso ON osoi.orderid = oso.id WHERE oso.addtime >= '+start_time_year+' AND oso.addtime < '+end_time_year+' AND osoi.departmentid = '+shopid+' GROUP BY osoi.brandid  ) as A ON ob.id = A.brandid WHERE ob.departmentid = '+orid+' ');

    return think.isEmpty(info)?this.json({'code':"200",'msg':"没有数据...",'data':''}):this.json({'code':"100",'msg':"查找成功...",'data':info});
  }

  //卡类经营分析表
  async getcardjingyingAction(){
    let shopid = this.post('shopid');
    let start_time = this.post('start_time_js');
    let end_time = this.post('end_time_js');
    let info = await this.model().query('SELECT osc.id,osc.name,osc.category,IFNULL(A.feat,0) as feat,IFNULL(A.guestnum,0) AS guestnum FROM `think_or_shop_card` osc LEFT JOIN (SELECT osoi.typeid,sum(osoi.amount) as feat,count(distinct oso.guestid) as guestnum FROM `think_or_shop_orderinfo` osoi LEFT JOIN `think_or_shop_order` oso ON osoi.orderid = oso.id WHERE oso.addtime >= '+start_time+' AND oso.addtime < '+end_time+' AND osoi.departmentid = '+shopid+' AND osoi.type = 3 GROUP BY typeid ) as A ON osc.id = A.typeid WHERE osc.shopid = '+shopid+' ');
    const mypath = think.config('mypath');
    let getapi = this.controller(mypath+'/database');
    let allguestnum =  await getapi.getshopallguestnum(shopid);
    return think.isEmpty(info)?this.json({'code':"200",'msg':"没有数据...",'data':''}):this.json({'code':"100",'msg':"查找成功...",'data':info,'allguestnum':allguestnum});
  }

  //卡类每月业绩
  async getkaleijingyingyejiAction(){
    let shopid = this.post('shopid');
    let year = this.post('year'); 
    //获取今年开始的时间戳
    var start_time_year =  Math.round(new Date(year,0, 1).getTime()/1000);
    //今年结束时间
    var end_time_year =  Math.round(new Date(Number(year) + 1,0, 1).getTime()/1000); 

    let info = await this.model().query('SELECT osc.id,osc.name,osc.category,IFNULL(A.Jan,0) as Jan,IFNULL(A.Feb,0) as Feb,IFNULL(A.Mar,0) as Mar,IFNULL(A.Apr,0) as Apr,IFNULL(A.May,0) as May,IFNULL(A.June,0) as June,IFNULL(A.July,0) as July,IFNULL(A.Aug,0) as Aug,IFNULL(A.Sept,0) as Sept,IFNULL(A.Oct,0) as Oct,IFNULL(A.Nov,0) as Nov,IFNULL(A.Dect,0) as Dect FROM `think_or_shop_card` osc LEFT JOIN (SELECT osoi.typeid,sum(if(month(FROM_UNIXTIME(oso.addtime))=1,osoi.amount,0)) as Jan,sum(if(month(FROM_UNIXTIME(oso.addtime))=2,osoi.amount,0)) as Feb,sum(if(month(FROM_UNIXTIME(oso.addtime))=3,osoi.amount,0)) as Mar,sum(if(month(FROM_UNIXTIME(oso.addtime))=4,osoi.amount,0)) as Apr,sum(if(month(FROM_UNIXTIME(oso.addtime))=5,osoi.amount,0)) as May,sum(if(month(FROM_UNIXTIME(oso.addtime))=6,osoi.amount,0)) as June,sum(if(month(FROM_UNIXTIME(oso.addtime))=7,osoi.amount,0)) as July,sum(if(month(FROM_UNIXTIME(oso.addtime))=8,osoi.amount,0)) as Aug,sum(if(month(FROM_UNIXTIME(oso.addtime))=9,osoi.amount,0)) as Sept,sum(if(month(FROM_UNIXTIME(oso.addtime))=10,osoi.amount,0)) as Oct,sum(if(month(FROM_UNIXTIME(oso.addtime))=11,osoi.amount,0)) as Nov,sum(if(month(FROM_UNIXTIME(oso.addtime))=12,osoi.amount,0)) as Dect FROM `think_or_shop_orderinfo` osoi LEFT JOIN `think_or_shop_order` oso ON osoi.orderid = oso.id WHERE oso.addtime >= '+start_time_year+' AND oso.addtime < '+end_time_year+' AND osoi.departmentid = '+shopid+' AND osoi.type = 3 GROUP BY osoi.typeid  ) as A ON osc.id = A.typeid WHERE osc.shopid = '+shopid+' ');
    return think.isEmpty(info)?this.json({'code':"200",'msg':"没有数据...",'data':''}):this.json({'code':"100",'msg':"查找成功...",'data':info});
  }

  //项目经营分析表
  async getxiangmujingyingAction(){
    let shopid = this.post('shopid');
    let start_time = this.post('start_time_js');
    let end_time = this.post('end_time_js');

    let info = await this.model().query('SELECT os.id,os.name as subjectname,ob.name as brandname,osc.name as categoryname,IFNULL(A.feat,0) as feat,IFNULL(A.guestnum,0) as guestnum FROM `think_or_shop_subject` oss LEFT JOIN `think_or_subject` os ON oss.subjectid = os.id LEFT JOIN `think_or_brand` ob ON os.brandid = ob.id LEFT JOIN `think_or_subjectcategory` osc ON os.categoryid = osc.id LEFT JOIN (SELECT osoi.typeid,sum(osoi.amount) as feat,count(distinct oso.guestid) as guestnum FROM `think_or_shop_orderinfo` osoi LEFT JOIN `think_or_shop_order` oso ON osoi.orderid = oso.id WHERE osoi.departmentid = '+shopid+' AND oso.addtime >= '+start_time+' AND oso.addtime < '+end_time+' AND osoi.type = 2 GROUP BY osoi.typeid) as A ON os.id = A.typeid WHERE oss.shopid = '+shopid+' ');

    const mypath = think.config('mypath');
    let getapi = this.controller(mypath+'/database');
    let allguestnum =  await getapi.getshopallguestnum(shopid);
    return think.isEmpty(info)?this.json({'code':"200",'msg':"没有数据...",'data':''}):this.json({'code':"100",'msg':"查找成功...",'data':info,'allguestnum':allguestnum});
  }

  //获取项目每月业绩
  async getxiangmujingyingyejiAction(){
    let shopid = this.post('shopid');
    let year = this.post('year'); 
    //获取今年开始的时间戳
    var start_time_year =  Math.round(new Date(year,0, 1).getTime()/1000);
    //今年结束时间
    var end_time_year =  Math.round(new Date(Number(year) + 1,0, 1).getTime()/1000); 

    let info = await this.model().query(' SELECT os.id,os.name as subjectname,IFNULL(A.Jan,0) as Jan,IFNULL(A.Feb,0) as Feb,IFNULL(A.Mar,0) as Mar,IFNULL(A.Apr,0) as Apr,IFNULL(A.May,0) as May,IFNULL(A.June,0) as June,IFNULL(A.July,0) as July,IFNULL(A.Aug,0) as Aug,IFNULL(A.Sept,0) as Sept,IFNULL(A.Oct,0) as Oct,IFNULL(A.Nov,0) as Nov,IFNULL(A.Dect,0) as Dect FROM `think_or_shop_subject` oss LEFT JOIN `think_or_subject` os ON oss.subjectid = os.id LEFT JOIN (SELECT osoi.typeid,sum(if(month(FROM_UNIXTIME(oso.addtime))=1,osoi.amount,0)) as Jan,sum(if(month(FROM_UNIXTIME(oso.addtime))=2,osoi.amount,0)) as Feb,sum(if(month(FROM_UNIXTIME(oso.addtime))=3,osoi.amount,0)) as Mar,sum(if(month(FROM_UNIXTIME(oso.addtime))=4,osoi.amount,0)) as Apr,sum(if(month(FROM_UNIXTIME(oso.addtime))=5,osoi.amount,0)) as May,sum(if(month(FROM_UNIXTIME(oso.addtime))=6,osoi.amount,0)) as June,sum(if(month(FROM_UNIXTIME(oso.addtime))=7,osoi.amount,0)) as July,sum(if(month(FROM_UNIXTIME(oso.addtime))=8,osoi.amount,0)) as Aug,sum(if(month(FROM_UNIXTIME(oso.addtime))=9,osoi.amount,0)) as Sept,sum(if(month(FROM_UNIXTIME(oso.addtime))=10,osoi.amount,0)) as Oct,sum(if(month(FROM_UNIXTIME(oso.addtime))=11,osoi.amount,0)) as Nov,sum(if(month(FROM_UNIXTIME(oso.addtime))=12,osoi.amount,0)) as Dect FROM `think_or_shop_orderinfo` osoi LEFT JOIN `think_or_shop_order` oso ON osoi.orderid = oso.id WHERE oso.addtime >= '+start_time_year+' AND oso.addtime < '+end_time_year+' AND osoi.departmentid = '+shopid+' AND osoi.type = 2 GROUP BY osoi.typeid  ) as A ON os.id = A.typeid WHERE oss.shopid = '+shopid+' ');

    return think.isEmpty(info)?this.json({'code':"200",'msg':"没有数据...",'data':''}):this.json({'code':"100",'msg':"查找成功...",'data':info});
  }

  //获取家居经营分析
  async getjiajujingyingAction(){
    let shopid = this.post('shopid');
    let start_time = this.post('start_time_js');
    let end_time = this.post('end_time_js');

    let info = await this.model().query(' SELECT og.id,og.name as goodsname,ob.name as brandname,IFNULL(A.feat,0) as feat,IFNULL(A.guestnum,0) as guestnum FROM `think_or_shop_goods` osg LEFT JOIN `think_or_goods` og ON osg.goodsid = og.id LEFT JOIN `think_or_brand` ob ON og.brandid = ob.id LEFT JOIN (SELECT osoi.typeid,sum(osoi.amount) as feat,count(distinct oso.guestid) as guestnum FROM `think_or_shop_orderinfo` osoi LEFT JOIN `think_or_shop_order` oso ON osoi.orderid = oso.id WHERE osoi.departmentid = '+shopid+' AND oso.addtime >= '+start_time+' AND oso.addtime < '+end_time+' and oso.status != 0 AND osoi.type = 1 GROUP BY osoi.typeid) as A ON og.id = A.typeid WHERE osg.shopid = '+shopid+' ');

    const mypath = think.config('mypath');
    let getapi = this.controller(mypath+'/database');
    let allguestnum =  await getapi.getshopallguestnum(shopid); 
    return think.isEmpty(info)?this.json({'code':"200",'msg':"没有数据...",'data':''}):this.json({'code':"100",'msg':"查找成功...",'data':info,'allguestnum':allguestnum});
  }

  //获取家居每月经营
  async getjiajujingyingyejiAction(){
    let shopid = this.post('shopid');
    let year = this.post('year'); 
    //获取今年开始的时间戳
    var start_time_year =  Math.round(new Date(year,0, 1).getTime()/1000);
    //今年结束时间
    var end_time_year =  Math.round(new Date(Number(year) + 1,0, 1).getTime()/1000); 

    let info = await this.model().query(' SELECT og.id,og.name as goodsname,IFNULL(A.Jan,0) as Jan,IFNULL(A.Feb,0) as Feb,IFNULL(A.Mar,0) as Mar,IFNULL(A.Apr,0) as Apr,IFNULL(A.May,0) as May,IFNULL(A.June,0) as June,IFNULL(A.July,0) as July,IFNULL(A.Aug,0) as Aug,IFNULL(A.Sept,0) as Sept,IFNULL(A.Oct,0) as Oct,IFNULL(A.Nov,0) as Nov,IFNULL(A.Dect,0) as Dect FROM `think_or_shop_goods` osg LEFT JOIN `think_or_goods` og ON osg.goodsid = og.id LEFT JOIN (SELECT osoi.typeid,sum(if(month(FROM_UNIXTIME(oso.addtime))=1,osoi.amount,0)) as Jan,sum(if(month(FROM_UNIXTIME(oso.addtime))=2,osoi.amount,0)) as Feb,sum(if(month(FROM_UNIXTIME(oso.addtime))=3,osoi.amount,0)) as Mar,sum(if(month(FROM_UNIXTIME(oso.addtime))=4,osoi.amount,0)) as Apr,sum(if(month(FROM_UNIXTIME(oso.addtime))=5,osoi.amount,0)) as May,sum(if(month(FROM_UNIXTIME(oso.addtime))=6,osoi.amount,0)) as June,sum(if(month(FROM_UNIXTIME(oso.addtime))=7,osoi.amount,0)) as July,sum(if(month(FROM_UNIXTIME(oso.addtime))=8,osoi.amount,0)) as Aug,sum(if(month(FROM_UNIXTIME(oso.addtime))=9,osoi.amount,0)) as Sept,sum(if(month(FROM_UNIXTIME(oso.addtime))=10,osoi.amount,0)) as Oct,sum(if(month(FROM_UNIXTIME(oso.addtime))=11,osoi.amount,0)) as Nov,sum(if(month(FROM_UNIXTIME(oso.addtime))=12,osoi.amount,0)) as Dect FROM `think_or_shop_orderinfo` osoi LEFT JOIN `think_or_shop_order` oso ON osoi.orderid = oso.id WHERE oso.addtime >= '+start_time_year+' AND oso.addtime < '+end_time_year+' AND osoi.departmentid = '+shopid+' and oso.status != 0 AND osoi.type = 1 GROUP BY osoi.typeid  ) as A ON og.id = A.typeid WHERE osg.shopid = '+shopid+' ');

    return think.isEmpty(info)?this.json({'code':"200",'msg':"没有数据...",'data':''}):this.json({'code':"100",'msg':"查找成功...",'data':info});
  }

  //获取产品当前库存和指定时间段销量
  async getproductksAction(){
    let shopid = this.post('shopid');
    let start_time = this.post('start_time_js');
    let end_time = this.post('end_time_js');

    let info = await this.model().query('SELECT A.id,A.name,IFNULL(A.kucun,0) as kucun,IFNULL(B.sales,0) as sales FROM ( SELECT sg.goodsid as id,g.name,SUM(sg.nums) as kucun FROM `think_or_shop_goods` sg LEFT JOIN `think_or_goods` g on sg.goodsid = g.id WHERE sg.shopid = '+shopid+' AND sg.isdisable = 0 GROUP BY sg.goodsid ) as A LEFT JOIN ( SELECT sgl.goodsid,SUM(sgl.nums) as sales FROM `think_or_shop_goodslog` sgl WHERE sgl.ordertype NOT IN ("RKD","CKD") AND sgl.addtime >= '+start_time+' AND sgl.addtime < '+end_time+' GROUP BY sgl.goodsid  ) as B ON A.id = B.goodsid ');

    return think.isEmpty(info)?this.json({'code':"200",'msg':"没有数据...",'data':''}):this.json({'code':"100",'msg':"查找成功...",'data':info});
  }


  // 获取门店经营分析
  async getMenDianJingYingAction () {
    let filter = this.get()
    const mypath = think.config('mypath');
    let api = this.controller(mypath+'/database');
    let yingYeShouRu = await api.getYingYeShouRu(filter)
    let qiTaShouRu = await api.getQiTaShouRu(Object.assign({}, filter, {type: 1}))
    let qiTaZhiChu = await api.getQiTaShouRu(Object.assign({}, filter, {type: 2}))
    let sunHao = await api.getSunHao(filter)
    let data = {
      yingYeShouRu: yingYeShouRu || 0,
      qiTaShouRu: qiTaShouRu || 0,
      qiTaZhiChu: qiTaZhiChu || 0,
      sunHao: sunHao || 0
    }
    console.log(data)
    return this.json({'code':"100",'msg':"查找成功...",'data':data})
  }

  //获取员工全年工资分析
  async getworkeryearpayAction(){
    //获取门店id和年份
    let shopid = this.post('shopid');
    let year = parseInt(this.post('year')); 
    //获取今年开始的时间戳
    let start_time_year =  Math.round(new Date(year,0, 1).getTime()/1000);
    //获取今年结束的时间戳
    let end_time_year =  Math.round(new Date(Number(year) + 1,0, 1).getTime()/1000); 

    let info = await this.model().query('SELECT au.id, au. name, IFNULL(p.Jan, 0) AS Jan, IFNULL(p.Feb, 0) AS Feb, IFNULL(p.Mar, 0) AS Mar, IFNULL(p.Apr, 0) AS Apr, IFNULL(p.May, 0) AS May, IFNULL(p.June, 0) AS June, IFNULL(p.July, 0) AS July, IFNULL(p.Aug, 0) AS Aug, IFNULL(p.Sept, 0) AS Sept, IFNULL(p.Oct, 0) AS Oct, IFNULL(p.Nov, 0) AS Nov, IFNULL(p.Dect, 0) AS Dect FROM `think_or_app_user` au LEFT JOIN ( SELECT au.id, sum(  IF ( MONTH (FROM_UNIXTIME(su.time)) = 1,  IF ( su.category = 6, 0 - su.money, su.money ), 0 ) ) AS Jan, sum(  IF ( MONTH (FROM_UNIXTIME(su.time)) = 2,  IF ( su.category = 6, 0 - su.money, su.money ), 0 ) ) AS Feb, sum(  IF ( MONTH (FROM_UNIXTIME(su.time)) = 3,  IF ( su.category = 6, 0 - su.money, su.money ), 0 ) ) AS Mar, sum(  IF ( MONTH (FROM_UNIXTIME(su.time)) = 4,  IF ( su.category = 6, 0 - su.money, su.money ), 0 ) ) AS Apr, sum(  IF ( MONTH (FROM_UNIXTIME(su.time)) = 5,  IF ( su.category = 6, 0 - su.money, su.money ), 0 ) ) AS May, sum(  IF ( MONTH (FROM_UNIXTIME(su.time)) = 6,  IF ( su.category = 6, 0 - su.money, su.money ), 0 ) ) AS June, sum(  IF ( MONTH (FROM_UNIXTIME(su.time)) = 7,  IF ( su.category = 6, 0 - su.money, su.money ), 0 ) ) AS July, sum(  IF ( MONTH (FROM_UNIXTIME(su.time)) = 8,  IF ( su.category = 6, 0 - su.money, su.money ), 0 ) ) AS Aug, sum(  IF ( MONTH (FROM_UNIXTIME(su.time)) = 9,  IF ( su.category = 6, 0 - su.money, su.money ), 0 ) ) AS Sept, sum(  IF ( MONTH (FROM_UNIXTIME(su.time)) = 10,  IF ( su.category = 6, 0 - su.money, su.money ), 0 ) ) AS Oct, sum(  IF ( MONTH (FROM_UNIXTIME(su.time)) = 11,  IF ( su.category = 6, 0 - su.money, su.money ), 0 ) ) AS Nov, sum(  IF ( MONTH (FROM_UNIXTIME(su.time)) = 12,  IF ( su.category = 6, 0 - su.money, su.money ), 0 ) ) AS Dect FROM `think_or_app_user` au LEFT JOIN `think_or_shop_userbasepay` su ON au.id = su.userid WHERE su.time >= '+start_time_year+' AND su.time < '+end_time_year+' GROUP BY au.id ) p ON p.id = au.id WHERE au.shopid = '+shopid+' AND au.isdisable = 0 AND au.isdelete = 0');

    return think.isEmpty(info)?this.json({'code':"200",'msg':"没有数据...",'data':''}):this.json({'code':"100",'msg':"查找成功...",'data':info});
  }

  //获取员工全年出勤分析
  async getworkeryearsignAction(){
    //获取门店id和年份
    let shopid = this.post('shopid');
    let year = parseInt(this.post('year')); 
    //获取今年开始的时间戳
    let start_time_year =  Math.round(new Date(year,0, 1).getTime()/1000);
    //获取今年结束的时间戳
    let end_time_year =  Math.round(new Date(Number(year) + 1,0, 1).getTime()/1000); 

    let info = await this.model().query('SELECT u.id,u.name, IFNULL(p.Jan, 0) AS Jan,IFNULL(p.Feb, 0) AS Feb,IFNULL(p.Mar, 0) AS Mar,IFNULL(p.Apr, 0) AS Apr,IFNULL(p.May, 0) AS May,IFNULL(p.June, 0) AS June,IFNULL(p.July, 0) AS July,IFNULL(p.Aug, 0) AS Aug,IFNULL(p.Sept, 0) AS Sept,IFNULL(p.Oct, 0) AS Oct,IFNULL(p.Nov, 0) AS Nov,IFNULL(p.Dect, 0) AS Dect FROM `think_or_app_user` u LEFT JOIN (SELECT w.userid,sum(  IF ( MONTH (FROM_UNIXTIME(w.time_start)) = 1, 1, 0 ) ) AS Jan,sum(  IF ( MONTH (FROM_UNIXTIME(w.time_start)) = 2, 1, 0 ) ) AS Feb,sum(  IF ( MONTH (FROM_UNIXTIME(w.time_start)) = 3, 1, 0 ) ) AS Mar,sum(  IF ( MONTH (FROM_UNIXTIME(w.time_start)) = 4, 1, 0 ) ) AS Apr,sum(  IF ( MONTH (FROM_UNIXTIME(w.time_start)) = 5, 1, 0 ) ) AS May,sum(  IF ( MONTH (FROM_UNIXTIME(w.time_start)) = 6, 1, 0 ) ) AS June,sum(  IF ( MONTH (FROM_UNIXTIME(w.time_start)) = 7, 1, 0 ) ) AS July,sum(  IF ( MONTH (FROM_UNIXTIME(w.time_start)) = 8, 1, 0 ) ) AS Aug,sum(  IF ( MONTH (FROM_UNIXTIME(w.time_start)) = 9, 1, 0 ) ) AS Sept,sum(  IF ( MONTH (FROM_UNIXTIME(w.time_start)) = 10, 1, 0 ) ) AS Oct,sum(  IF ( MONTH (FROM_UNIXTIME(w.time_start)) = 11, 1, 0 ) ) AS Nov,sum(  IF ( MONTH (FROM_UNIXTIME(w.time_start)) = 12, 1, 0 ) ) AS Dect FROM `think_or_user_work` w WHERE w.type = 1 AND w.time_start >= '+start_time_year+' AND w.time_start < '+end_time_year+' GROUP BY w.userid) as p on u.id = p.userid WHERE u.shopid = '+shopid+' AND u.isdisable = 0 AND u.isdelete = 0');

    return think.isEmpty(info)?this.json({'code':"200",'msg':"没有数据...",'data':''}):this.json({'code':"100",'msg':"查找成功...",'data':info});
  }

  //-------------------------------------------------------客户类型

  //获取客户的性别分类
  async getguestsexAction(){
    let departmentid = this.post('departmentid');
    let info = await this.model().query('SELECT sex FROM `think_or_guest` WHERE departmentid in ('+departmentid+') ');
    return think.isEmpty(info)?this.json({'code':"200",'msg':"没有数据...",'data':''}):this.json({'code':"100",'msg':"查找成功...",'data':info});
  }

  //获取客户年龄分类
  async getguestoldAction(){
    let departmentid  = this.post('departmentid');
    let info = await this.model().query('SELECT IFNULL(oga.birthyear,0) as year FROM `think_or_guest` og LEFT JOIN `think_or_guest_archives` oga ON og.id = oga.guestid WHERE og.departmentid in ('+departmentid+') ');
    return think.isEmpty(info)?this.json({'code':"200",'msg':"没有数据...",'data':''}):this.json({'code':"100",'msg':"查找成功...",'data':info});
  }



};
